

* Please change the “path” to the working directory to run the code

global path "data"
global path_raw "data/raw"
global path_analysis "data/analysis_data"
global path_output "data/output_tables_figures"

/*----------------------------------------------------------------------------*/

// ATTOM Data //

* The data is proprietary, owned by ATTOM.
* I provide pseudo data in the replication package. 

clear
import delimited "$path_raw/attom_sfr_zip_year.csv" // Pseudo data "attom_sfr_zip_year_pseudo.csv" is provided in /data/raw/

tostring zip_code, replace
rename zip_code zip
replace zip="0000"+zip if length(zip)==1
replace zip="000"+zip if length(zip)==2
replace zip="00"+zip if length(zip)==3
replace zip="0"+zip if length(zip)==4

save "$path_analysis/attom_zip_year.dta", replace
/*----------------------------------------------------------------------------*/

* Assign 0 to years with no transaction (currently coded as missing)

use "$path_analysis/attom_zip_year.dta", clear
bysort zip: keep if _n==1
keep zip
forvalues i=2007/2018 {
	gen x`i'=0
}
reshape long x, i(zip) j(year)
sort zip year

merge 1:1 zip year using "$path_analysis/attom_zip_year.dta"
sort zip year

foreach x in n_transaction n_cash n_mortgage {
	replace `x'=0 if _merge==1
}
drop x _merge

save "$path_analysis/attom_zip_year.dta", replace

/*----------------------------------------------------------------------------*/

* Use the ZIP to ctn crosswalk
use "$path_raw/HUD_ZIP_TRACT_crosswalk.dta", clear
joinby zip using "$path_analysis/attom_zip_year.dta"

sort year zip ctn
order year ctn zip w

sort year ctn zip

foreach x in n_transaction n_cash n_mortgage {
	replace `x'=w*`x'
}

foreach x in n_transaction n_cash n_mortgage {
	by year ctn: egen sum_`x'=sum(`x')
}

by year ctn: keep if _n==1

foreach x in n_transaction n_cash n_mortgage {
	drop `x'
	rename sum_`x' `x'
}

order ctn year
sort ctn year

gen cash_ratio=n_cash/n_transaction

keep ctn year n_transaction n_cash n_mortgage cash_ratio

save "$path_analysis/attom_ctn_year.dta", replace
erase "$path_analysis/attom_zip_year.dta"

/*----------------------------------------------------------------------------*/
